# SQLite3数据库CRUD操作示例

"""
SQL基础知识：

1. SQL语句类型：
   - DDL（数据定义语言）：CREATE, DROP, ALTER等
   - DML（数据操作语言）：INSERT, UPDATE, DELETE等
   - DQL（数据查询语言）：SELECT等

2. 常用SQL语句：
   - CREATE TABLE：创建表
   - INSERT INTO：插入数据
   - SELECT：查询数据
   - UPDATE：更新数据
   - DELETE：删除数据
   - DROP TABLE：删除表

3. WHERE子句：
   - 用于筛选符合条件的记录
   - 支持比较运算符：=, >, <, >=, <=, !=
   - 支持逻辑运算符：AND, OR, NOT

4. ORDER BY：
   - 用于对结果进行排序
   - ASC：升序（默认）
   - DESC：降序

5. LIKE：
   - 用于模糊匹配
   - %：匹配任意多个字符
   - _：匹配单个字符
"""

import sqlite3


def create_table(cursor):
    """创建用户信息表"""
    # 使用CREATE TABLE语句创建表
    # INTEGER PRIMARY KEY AUTOINCREMENT：自增主键
    # NOT NULL：不允许为空
    # DEFAULT：设置默认值
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE,
        register_date TEXT DEFAULT CURRENT_TIMESTAMP
    )
    """)
    print("用户表创建成功！")


def insert_data(cursor):
    """插入数据示例"""
    try:
        # 1. 插入单条数据
        cursor.execute(
            """
        INSERT INTO users (name, age, email) 
        VALUES (?, ?, ?)
        """,
            ("张三", 25, "zhangsan@example.com"),
        )
        print("插入单条数据成功！")

        # 2. 批量插入数据
        users_data = [
            ("李四", 30, "lisi@example.com"),
            ("王五", 28, "wangwu@example.com"),
            ("赵六", 35, "zhaoliu@example.com"),
        ]
        cursor.executemany(
            """
        INSERT INTO users (name, age, email) 
        VALUES (?, ?, ?)
        """,
            users_data,
        )
        print("批量插入数据成功！")

    except sqlite3.IntegrityError as e:
        print(f"插入数据出错：{e}")


def select_data(cursor):
    """查询数据示例"""
    # 1. 查询所有数据
    print("\n1. 查询所有用户：")
    cursor.execute("SELECT * FROM users")
    all_users = cursor.fetchall()
    for user in all_users:
        print(f"用户信息：{user}")

    # 2. 条件查询
    print("\n2. 查询年龄大于28岁的用户：")
    cursor.execute("SELECT name, age FROM users WHERE age > ?", (28,))
    older_users = cursor.fetchall()
    for user in older_users:
        print(f"用户名：{user[0]}, 年龄：{user[1]}")

    # 3. 模糊查询
    print("\n3. 查询名字包含'三'的用户：")
    cursor.execute("SELECT name, email FROM users WHERE name LIKE ?", ("%三%",))
    matched_users = cursor.fetchall()
    for user in matched_users:
        print(f"用户名：{user[0]}, 邮箱：{user[1]}")

    # 4. 排序查询
    print("\n4. 按年龄降序查询用户：")
    cursor.execute("SELECT name, age FROM users ORDER BY age DESC")
    sorted_users = cursor.fetchall()
    for user in sorted_users:
        print(f"用户名：{user[0]}, 年龄：{user[1]}")


def update_data(cursor):
    """更新数据示例"""
    # 1. 更新单个字段
    cursor.execute(
        """
    UPDATE users 
    SET age = ? 
    WHERE name = ?
    """,
        (26, "张三"),
    )
    print(f"更新影响的行数：{cursor.rowcount}")

    # 2. 更新多个字段
    cursor.execute(
        """
    UPDATE users 
    SET age = ?, email = ? 
    WHERE name = ?
    """,
        (31, "lisi_new@example.com", "李四"),
    )
    print(f"更新影响的行数：{cursor.rowcount}")


def delete_data(cursor):
    """删除数据示例"""
    # 1. 删除指定数据
    cursor.execute("DELETE FROM users WHERE name = ?", ("赵六",))
    print(f"删除影响的行数：{cursor.rowcount}")

    # 2. 条件删除
    cursor.execute("DELETE FROM users WHERE age > ?", (30,))
    print(f"删除影响的行数：{cursor.rowcount}")


def drop_table(cursor):
    """删除表示例"""
    cursor.execute("DROP TABLE IF EXISTS users")
    print("用户表删除成功！")


def main():
    # 连接到SQLite数据库（如果不存在则创建）
    conn = sqlite3.connect("user_management.db")
    cursor = conn.cursor()

    try:
        print("=== SQLite3数据库CRUD操作示例 ===")

        # 1. 创建表
        print("\n--- 1. 创建表 ---")
        create_table(cursor)

        # 2. 插入数据
        print("\n--- 2. 插入数据 ---")
        insert_data(cursor)

        # 3. 查询数据
        print("\n--- 3. 查询数据 ---")
        select_data(cursor)

        # 4. 更新数据
        print("\n--- 4. 更新数据 ---")
        update_data(cursor)
        print("\n更新后的数据：")
        select_data(cursor)

        # 5. 删除数据
        print("\n--- 5. 删除数据 ---")
        delete_data(cursor)
        print("\n删除后的数据：")
        select_data(cursor)

        # 6. 删除表（演示完成后清理）
        print("\n--- 6. 删除表 ---")
        drop_table(cursor)

        # 提交事务
        # 使用事务来确保所有操作要么全部成功，要么全部回滚
        # 在sqlite中，事务是自动开始的，但是要生效，需要手动提交，主要是针对DML的操作，DDL的操作不需要手动提交即可生效
        conn.commit()
        print("\n所有操作已成功提交！")

    except sqlite3.Error as e:
        print(f"\n操作出错：{e}")
        conn.rollback()
        print("已回滚所有操作！")

    finally:
        # 关闭连接
        cursor.close()
        conn.close()
        print("\n数据库连接已关闭！")


if __name__ == "__main__":
    main()
